package com.cloudlead.web.resource;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.*;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.*;
public class UtilExcel extends HttpServlet{
	/**
	 * 按EasyuiGridData数据导出Excel
	 * @param gridData
	 * @param columns
	 * @param response
	 * @throws Exception
	 */
	public InputStream ExportToExcel(ClientData gridData,String columns,HttpServletResponse response) throws Exception{
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("DataList");
		//取列定义
		int rowIdx=0;
		HSSFFont headfont = wb.createFont();
		headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		HSSFCellStyle headStyle=wb.createCellStyle();
		//headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		//headStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
		headStyle.setFont(headfont);
		headStyle.setAlignment(CellStyle.ALIGN_CENTER);
		headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		HSSFRow head;
		int pos=columns.indexOf("||");
		if (pos>0){
			HSSFRow topRow = sheet.createRow(rowIdx++);
			head = sheet.createRow(rowIdx++);
			int colIdx=0;
			for (String item:columns.substring(0,pos).split("#")){
				String[] col=item.split(",");
				int rowspan=Integer.parseInt(col[1]);
				int colspan=Integer.parseInt(col[2]);
				HSSFCell cell=topRow.createCell(colIdx);
				cell.setCellStyle(headStyle);
				cell.setCellValue(col[0]);
				if (rowspan>1 || colspan>1)
					sheet.addMergedRegion(new CellRangeAddress(0,rowspan-1,colIdx,colIdx+colspan-1));
				colIdx+=colspan;
			}
			columns=columns.substring(pos+2);
		}else{
			head = sheet.createRow(rowIdx++);
		}
		String[] columnArry= columns.split("#");
		for (int idx=0;idx<columnArry.length;idx++) {
			String[] col=columnArry[idx].split(",");
			sheet.setColumnWidth(idx, Integer.valueOf(col[2])*50);
			HSSFCell cell=head.createCell(idx);
			cell.setCellStyle(headStyle);
			cell.setCellValue(col[0]);
		}
		//        head.setRowStyle(headStyle);
		int rowNum=1;
		for (Object obj:gridData.getRows()){
			HSSFRow dataRow = sheet.createRow(rowIdx++);
			for (int idx=0;idx<columnArry.length;idx++) {
				String[] col=columnArry[idx].split(",");
				HSSFCell cell=dataRow.createCell(idx);
				Object objVal=UtilFuns.GetFieldObjValue(obj, col[1]);
				if (col[1].equalsIgnoreCase("ROW_ID")) objVal=rowNum++;
				if (objVal instanceof Double)
					cell.setCellValue((Double)objVal);
				else if (objVal instanceof Integer)
					cell.setCellValue((Integer)objVal);
				else if (objVal instanceof BigDecimal)
					cell.setCellValue(((BigDecimal)objVal).doubleValue());
				else
					cell.setCellValue(UtilFuns.GetFieldValue(obj,col[1]));
			}
		}
/**	        if (gridData.getFooter()!=null){
 HSSFRow footRow = sheet.createRow(rowIdx);
 for (int idx=0;idx<columnArry.length;idx++) {
 String[] col=columnArry[idx].split(",");
 HSSFCell cell=footRow.createCell(idx);
 cell.setCellStyle(headStyle);
 Object objVal=UtilFuns.GetFieldObjValue(gridData.getFooter().get(0), col[1]);
 if (objVal instanceof Double)
 cell.setCellValue((Double)objVal);
 else if (objVal instanceof Integer)
 cell.setCellValue((Integer)objVal);
 else
 cell.setCellValue(UtilFuns.GetFieldValue(gridData.getFooter().get(0),col[1]));
 }
 }*/
		response.setContentType("application/msexcel;charset=gbk");
		response.setHeader("Content-disposition", "GF4; filename=" + UtilFuns.formatDate(new Date(), "yyyyMMddHHmmss") + ".xls");
		response.setCharacterEncoding("gbk");
		wb.write(response.getOutputStream());
		InputStream in = new FileInputStream(String.valueOf(wb));
		return  in;
	}
}
